package com.horay.springbootdemo.jdbc.template;

import com.horay.springbootdemo.common.model.Foo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 服务启动类
 *
 * @author heyingcheng
 * @date 2019/10/31 21:29
 */
@Slf4j
@SpringBootApplication
public class JdbcTemplateApplcation implements CommandLineRunner {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
    @Autowired
    private SimpleJdbcInsert simpleJdbcInsert;

    public static void main(String[] args) {
        SpringApplication.run(JdbcTemplateApplcation.class, args);
    }

    @Bean
    public SimpleJdbcInsert simpleJdbcInsert() {
        return new SimpleJdbcInsert(jdbcTemplate).withTableName(Foo.TABLE_NAME).usingGeneratedKeyColumns(Foo.ID);
    }

    @Override
    public void run(String... args) throws Exception {
        insert();
        count();
        queryForList();

        update();
        count();
        query();

        delete();
        count();
        query();

        batchInsert();
        count();
        query();
    }

    private void batchInsert() {
        jdbcTemplate.batchUpdate(Foo.INSERT_SQL, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1,"N-".concat(String.valueOf(i)));
            }

            @Override
            public int getBatchSize() {
                return 2;
            }
        });

        List<Foo> list = new ArrayList<>();
        list.add(Foo.builder().fName("GGGGGG").build());
        list.add(Foo.builder().fName("HHHHHH").build());
        namedParameterJdbcTemplate.batchUpdate("INSERT INTO foo(f_name) VALUES (:fName)", SqlParameterSourceUtils.createBatch(list));
    }

    private void count() {
        Long count = jdbcTemplate.queryForObject(Foo.SELECT_COUNT_SQL, Long.class);
        log.info("count:[{}]", count);
    }

    private void queryForList() {
        jdbcTemplate.queryForList(Foo.SELECT_SQL).forEach(row -> log.info(row.toString()));
    }

    private void query() {
        jdbcTemplate.query(Foo.SELECT_SQL, (rs, i) -> Foo.builder().id(rs.getInt(Foo.ID)).fName(rs.getString(Foo.F_NAME)).build()).forEach(foo -> log.info(foo.toString()));
    }

    private void insert() {
        Arrays.asList("AAAAAA", "BBBBBB").forEach(n -> {
            jdbcTemplate.update(Foo.INSERT_SQL, n);
        });

        Map<String, Object> map = new HashMap<>();
        map.put(Foo.F_NAME, "EEEEEE");
        Number number = simpleJdbcInsert.executeAndReturnKey(map);
        log.info("number:[{}]", number.longValue());
    }

    private void update() {
        jdbcTemplate.update(Foo.UPDATE_BY_ID_SQL, "CCCDDD", 1);
    }

    private void delete() {
        jdbcTemplate.update(Foo.DELETE_BY_ID_SQL, 2);
    }

}
